﻿'''
分数数据与CF系数处理模块
'''
# 导入需要的库
import pandas as pd
from sqlalchemy import create_engine

# 创建SQL server 数据库连接
engine = create_engine(
    "mssql+pyodbc://pereader:pereader@172.18.65.31/SortingDB?driver=ODBC+Driver+17+for+SQL+Server"
)
# 显示所有的列 方便调试
pd.set_option('display.max_columns', None)


# 根据工单获取品号
def get_item_code(MoID):
    item_code = pd.read_sql(
        f"SELECT top 1 ITEM_CODE FROM MO_ITEM WHERE DOC_NO='{MoID}';", engine)
    if item_code.empty is False:
        item_code1 = item_code.loc[0, 'ITEM_CODE']
    else:
        item_code1 = ''
    return item_code1


def get_standard_value(item_code):
    standard_value = pd.read_sql(
        f"SELECT TOP 1 FEATURE_VALUE FROM ITEM_CODE_VALUE WHERE ITEM_CODE='{item_code}' AND FEATURE_NAME = '质量标准'",
        engine)
    if standard_value.empty:
        standard_value = ''
    else:
        standard_value = standard_value.loc[0, 'FEATURE_VALUE']
    return standard_value


# 根据工单获取冷热态信息
def get_cold_or_hot(MoID):
    df = pd.read_sql("SELECT * FROM ITEM_CODE_VALUE WHERE ITEM_CODE = (SELECT TOP 1  ITEM_CODE FROM MO_ITEM "
                     f"WHERE DOC_NO='{MoID}') ", engine)
    # 防呆,排除工单号错误的情况
    if len(df) != 0:
        is_cold = df[df['FEATURE_NAME'] == '冷态流明Max(lm)']['FEATURE_VALUE'].tolist()[0]
        if is_cold:
            return 25
        else:
            return 85
    else:
        return None


# 根据工单获得是否为CG产品
def get_flag(MoID):
    item_name = pd.read_sql(
        f"SELECT top 1 ITEM_NAME FROM MO_ITEM WHERE DOC_NO='{MoID}';", engine)
    if not item_name.empty and item_name.loc[0, 'ITEM_NAME'].startswith('CG'):
        return True
    else:
        return False


# CF2,CF3参数获取
def get_cf(item_code, cf):
    """
    :param item_code:品号
    :param cf: 2为CF2,3为CF3
    :return: 数据状态与dataframe
    """
    if not item_code:
        df_CF = pd.DataFrame()
        return df_CF
    cf_sel = {
        2: ('CF2-调整系数代码', 'CF2_LEDteen', 'CF2_Code'),
        3: ('CF3-对标系数代码', 'CF3_Customer', 'CF3_Code')
    }
    df_CF = pd.read_sql(
        f"SELECT * FROM ITEM_CODE_VALUE,{cf_sel[cf][1]} WHERE ITEM_CODE='{item_code}' "
        f"AND FEATURE_NAME='{cf_sel[cf][0]}' AND DataStatus=1 AND "
        f"ITEM_CODE_VALUE.FEATURE_VALUE={cf_sel[cf][1]}.{cf_sel[cf][2]} "
        "collate Chinese_PRC_CI_AS", engine)
    return df_CF


# 标准件生效数据获取
def get_standpart_data(item_code):
    """

    :param item_code:
    :return:
    """
    df_standpart_data = pd.read_sql(
        "SELECT T1.ITEM_CODE,T1.ITEM_NAME,T2.StandardPartID,T2.StandardPartNO,T2.Current_mA AS I_y,"
        "T2.ForwardVoltage_V AS U_y,T2.CIEx AS x_y,T2.CIEY AS y_y,T2.LuminousFlux_lm AS lm_y, "
        "T2.Ra AS Ra_y,T2.R9 AS R9_y FROM ITEM_CODE_VALUE as T1, StandardPartData as T2 "
        "WHERE T1.FEATURE_VALUE=T2.StandardPartID collate Chinese_PRC_CI_AS "
        f"AND T1.FEATURE_NAME='标准件型号' AND T1.ITEM_CODE='{item_code}' AND T2.DataStatus=1;",
        engine)
    # 除去重复数据
    df_standpart_data.drop_duplicates(subset='StandardPartNO',
                                      keep='last',
                                      inplace=True)
    return df_standpart_data


# 数据加系数
def cal_coef(standpart_data, test_data):
    """
    :param standpart_data:
    :param test_data:
    :return:
    """
    df_cal_data1 = pd.DataFrame()
    try:
        test_data = test_data.rename(
            columns={
                'TestNO': 'StandardPartNO',
                'Current_mA': 'I_x',
                'ForwardVoltage_V': 'U_x',
                'LuminousFlux_lm': 'lm_x',
                'CIEx': 'x_x',
                'CIEy': 'y_x',
                'Ra': 'Ra_x',
                'R9': 'R9_x'
            })
        test_data1 = test_data.loc[:, [
                                          'StandardPartNO', 'TestTime', 'I_x', 'U_x', 'lm_x', 'x_x', 'y_x',
                                          'Ra_x', 'R9_x'
                                      ]]
        # print(test_data1)
        # print(standpart_data)
        df_cal_data = pd.merge(standpart_data,
                               test_data1,
                               how='inner',
                               on='StandardPartNO')
        # print(df_cal_data)
    except:
        df_cal_data = pd.DataFrame()
    if not df_cal_data.empty:
        # 生产参数列表
        pras = [['dU', 'U_y', 'U_x'], ['dx', 'x_y', 'x_x'],
                ['dy', 'y_y', 'y_x'], ['dRa', 'Ra_y', 'Ra_x'],
                ['dR9', 'R9_y', 'R9_x']]
        for i in pras:
            df_cal_data[i[0]] = df_cal_data[i[1]] - df_cal_data[i[2]]

        df_cal_data['dlm'] = df_cal_data['lm_y'] / df_cal_data['lm_x']
        df_cal_data['dI'] = (abs(df_cal_data['I_y'] - df_cal_data['I_x'])) / df_cal_data['I_y']
        df_cal_data1 = df_cal_data.loc[:, [
                                              'ITEM_CODE', 'StandardPartNO', 'dU', 'dx', 'dy', 'dRa', 'dR9',
                                              'dlm', 'dI'
                                          ]]
        pras = [['dU', 'U_y', 'U_x'], ['dx', 'x_y', 'x_x'],
                ['dy', 'y_y', 'y_x'], ['dRa', 'Ra_y', 'Ra_x'],
                ['dR9', 'R9_y', 'R9_x']]
        # 对各个系数进行求平均值
        for i in pras:
            df_cal_data1.loc['校准系数', [i[0]]] = df_cal_data[i[0]].mean()
        df_cal_data1.loc['校准系数', 'dlm'] = df_cal_data['dlm'].mean()
        df_cal_data1.loc['校准系数', 'dI'] = df_cal_data['dI'].mean()
        df_cal_data1.loc['校准系数', 'ITEM_CODE'] = df_cal_data.loc[0, 'ITEM_CODE']
        # print(df_cal_data1)
        return df_cal_data1
    else:
        return df_cal_data


# 验证校准是否有效并计算最终系数，在原有系数上面叠加CF2和CF3
def cal_end_coef(coef_data, cf2_data, cf3_data):
    """
    :param coef_data: 中间系数，数据类型为字典
    :param cf2: cf2数据
    :param cf3: cf3数据
    :return: df_end_coef 最终系数数据
    """
    # 获取未加cf2，cf3的校准系数
    coef = pd.Series(coef_data)
    # 创建加cf2，cf3的校准系数，并进行运算
    end_coef = coef.copy()
    pras = [['dU', 'ForwardVoltage_offset'], ['dx', 'CIEx_offset'],
            ['dy', 'CIEy_offset'], ['dRa', 'CRI_offset'], ['dR9', 'R9_offset']]
    for i in pras:
        end_coef[
            i[0]] = coef[i[0]] + cf2_data.loc[0, i[1]] + cf3_data.loc[0, i[1]]
    end_coef['dlm'] = coef['dlm'] * cf2_data.loc[
        0, 'LuminousFlux_gain'] * cf3_data.loc[0, 'LuminousFlux_gain']
    df_end_coef = end_coef.to_frame().T.rename(index={0: "最终系数"})
    return df_end_coef


# 测试数据加系数运算,统一按y=kx+b进行线性计算
def cal_end_data(test_data, cf_data):
    """
    :param test_data:测试数据
    :param cf_data:cf2 或cf 3
    :return:
    """
    pras = [['ForwardVoltage_V', 'ForwardVoltage_gain', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_gain', 'CIEx_offset'],
            ['CIEy', 'CIEy_gain', 'CIEy_offset'], ['LuminousFlux_lm', 'LuminousFlux_gain', 'LuminousFlux_offset'],
            ['Ra', 'CRI_gain', 'CRI_offset'], ['R9', 'R9_gain', 'R9_offset']]
    cal_data = test_data.copy()
    # 全部安装y=kx+b进行线性计算
    for i in pras:
        cal_data[i[0]] = test_data.loc[:, [i[0]]].apply(
            lambda x: cf_data.loc[0, i[1]] * x + cf_data.loc[0, i[2]])
    return cal_data


# 远方机测试数据直接对数据进行CF2,CF3的叠加(系数叠加仿照cal_end_coef)
def add_cf_data(data, cf2_data, cf3_data, logger, extra_data=pd.DataFrame()):
    if cf2_data.empty:
        if cf3_data.empty:
            if extra_data.empty:
                logger.info('没有 CF2/CF3/extra 系数')
                return data
            else:
                end_coef = data.copy()
                extra_data = extra_data.rename(columns={'RA_offset': 'CRI_offset'})
                pras = [['ForwardVoltage_V', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_offset'],
                        ['CIEy', 'CIEy_offset'], ['Ra', 'CRI_offset'], ['R9', 'R9_offset']]
                for i in pras:
                    end_coef[i[0]] = data[i[0]] + extra_data.loc[0, i[1]]
                end_coef['LuminousFlux_lm'] = data['LuminousFlux_lm'] * extra_data.loc[0, 'LuminousFlux_gain']
                logger.info('没有 CF2/CF3 系数,有extra系数')
                return end_coef
        else:
            if extra_data.empty:
                end_coef = data.copy()
                pras = [['ForwardVoltage_V', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_offset'],
                        ['CIEy', 'CIEy_offset'], ['Ra', 'CRI_offset'], ['R9', 'R9_offset']]
                for i in pras:
                    end_coef[i[0]] = data[i[0]] + cf3_data.loc[0, i[1]]
                end_coef['LuminousFlux_lm'] = data['LuminousFlux_lm'] * cf3_data.loc[0, 'LuminousFlux_gain']
                logger.info('没有 CF2/extra 系数,有CF3系数')
            else:
                extra_data = extra_data.rename(columns={'RA_offset': 'CRI_offset'})
                end_coef = data.copy()
                pras = [['ForwardVoltage_V', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_offset'],
                        ['CIEy', 'CIEy_offset'], ['Ra', 'CRI_offset'], ['R9', 'R9_offset']]
                for i in pras:
                    end_coef[i[0]] = data[i[0]] + cf3_data.loc[0, i[1]] + extra_data.loc[0, i[1]]
                end_coef['LuminousFlux_lm'] = data['LuminousFlux_lm'] * cf3_data.loc[0, 'LuminousFlux_gain'] * \
                                              extra_data.loc[0, 'LuminousFlux_gain']
                logger.info('没有 CF2 系数,有CF3/extra系数')
    else:
        if cf3_data.empty:
            if extra_data.empty:
                end_coef = data.copy()
                pras = [['ForwardVoltage_V', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_offset'],
                        ['CIEy', 'CIEy_offset'], ['Ra', 'CRI_offset'], ['R9', 'R9_offset']]
                for i in pras:
                    end_coef[i[0]] = data[i[0]] + cf2_data.loc[0, i[1]]
                end_coef['LuminousFlux_lm'] = data['LuminousFlux_lm'] * cf2_data.loc[0, 'LuminousFlux_gain']
                logger.info('没有 extra/CF3 系数,有CF2系数')
                return end_coef
            else:
                extra_data = extra_data.rename(columns={'RA_offset': 'CRI_offset'})
                end_coef = data.copy()
                pras = [['ForwardVoltage_V', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_offset'],
                        ['CIEy', 'CIEy_offset'], ['Ra', 'CRI_offset'], ['R9', 'R9_offset']]
                for i in pras:
                    end_coef[i[0]] = data[i[0]] + cf2_data.loc[0, i[1]] + extra_data.loc[0, i[1]]
                end_coef['LuminousFlux_lm'] = data['LuminousFlux_lm'] * cf2_data.loc[0, 'LuminousFlux_gain'] * \
                                              extra_data.loc[0, 'LuminousFlux_gain']
                logger.info('没有 CF3 系数,有extra/CF2系数')
                return end_coef
        else:
            if extra_data.empty:
                end_coef = data.copy()
                pras = [['ForwardVoltage_V', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_offset'],
                        ['CIEy', 'CIEy_offset'], ['Ra', 'CRI_offset'], ['R9', 'R9_offset']]
                for i in pras:
                    end_coef[i[0]] = data[i[0]] + cf2_data.loc[0, i[1]] + cf3_data.loc[0, i[1]]
                end_coef['LuminousFlux_lm'] = data['LuminousFlux_lm'] * cf2_data.loc[0, 'LuminousFlux_gain'] * \
                                              cf3_data.loc[0, 'LuminousFlux_gain']
                logger.info('没有 CF3 系数,有extra/CF2系数')
                return end_coef
            else:
                extra_data = extra_data.rename(columns={'RA_offset': 'CRI_offset'})
                end_coef = data.copy()
                pras = [['ForwardVoltage_V', 'ForwardVoltage_offset'], ['CIEx', 'CIEx_offset'],
                        ['CIEy', 'CIEy_offset'], ['Ra', 'CRI_offset'], ['R9', 'R9_offset']]
                for i in pras:
                    end_coef[i[0]] = data[i[0]] + cf2_data.loc[0, i[1]] + cf3_data.loc[0, i[1]] + extra_data.loc[
                        0, i[1]]
                end_coef['LuminousFlux_lm'] = data['LuminousFlux_lm'] * cf2_data.loc[0, 'LuminousFlux_gain'] * \
                                              cf3_data.loc[0, 'LuminousFlux_gain'] * extra_data.loc[
                                                  0, 'LuminousFlux_gain']
                logger.info('有extra/CF2/CF3系数')
                return end_coef


# 测试机数据提取转换方法
def get_test_data(file_path, machine_name, pluse):
    df_test_data = pd.DataFrame()
    error_string = ''
    try:
        if '测试' in machine_name:
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', sep='\t', header=0,
                             usecols=['序号', '时间', 'I(A)', 'U(V)', 'P(W)',
                                      'Φ(lm)', 'Φe(mW)', '光效(lm/W)',
                                      'x', 'y', "u'", "v'", 'CCT(K)',
                                      '主波长(nm)', '峰值波长(nm)', '半峰带宽(nm)',
                                      'Ra', 'R1', 'R2', 'R3', 'R4', 'R5',
                                      'R6', 'R7', 'R8', 'R9', 'R10', 'R11', 'R12', 'R13',
                                      'R14', 'R15', 'SDCM', 'Tb'])

            df_test_data = df_test_data.rename(
               columns={'序号': 'TestNO', '时间': 'TestTime', 'I(A)': 'Current_mA', 'U(V)': 'ForwardVoltage_V', 'P(W)': 'Power_W', \
                     'Φ(lm)': 'LuminousFlux_lm', 'Φe(mW)': 'RadiantFlux_mW', '光效(lm/W)': 'LumiousEfficacy_lmPerW', \
                     'x': 'CIEx', 'y': 'CIEy', "u'": 'CIEu_1976', "v'": 'CIEv_1976', 'CCT(K)': 'CCT_K', \
                     '主波长(nm)': 'DominantWavelength_nm', '峰值波长(nm)': 'PeakWavelength_nm', '半峰带宽(nm)': 'FWHM_nm', \
                     'Ra': 'Ra', 'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', \
                     'R6': 'R6', 'R7': 'R7', 'R8': 'R8', 'R9': 'R9', 'R10': 'R10', 'R11': 'R11', 'R12': 'R12', 'R13': 'R13', \
                     'R14': 'R14', 'R15': 'R15', 'SDCM': 'SDCM', 'Tb': 'TestTemperature'})
            df_test_data['Current_mA']=df_test_data['Current_mA']*1000
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '多谱分光机':
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='utf_16', sep='\t', header=6, \
                             usecols=['NO', 'BIN号', 'VF', 'Φv', 'CIE-x', 'CIE-y', 'Ra', 'Tc', 'λd', 'λp', \
                                      "CIE-u'", "CIE-v'", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', \
                                      'R9', 'R10', 'R11', 'R12', 'R13', 'R14', 'R15', 'IF', 'Pow', 'SDCM', 'BIN名', '时间', \
                                      '光功率', '光效'])
            # 输出未处理的结果，方便代码调试
            df_test_data['光功率'] = df_test_data['光功率'] * 1000
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'NO': 'TestNO', 'BIN号': 'BinID', 'VF': 'ForwardVoltage_V', 'Φv': 'LuminousFlux_lm', \
                                    'CIE-x': 'CIEx', 'CIE-y': 'CIEy', \
                                    'Ra': 'Ra', 'Tc': 'CCT_K', 'λd': 'DominantWavelength_nm', 'λp': 'PeakWavelength_nm', \
                                    "CIE-u'": 'CIEu_1976', "CIE-v'": 'CIEv_1976', \
                                    'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7',
                                    'R8': 'R8', \
                                    'R9': 'R9', 'R10': 'R10', 'R11': 'R11', \
                                    'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15', 'IF': 'Current_mA', \
                                    'Pow': 'Power_W', 'SDCM': 'SDCM', 'BIN名': 'BinName', '时间': 'TestTime',
                                    '光功率': 'RadiantFlux_mW', \
                                    '光效': 'LumiousEfficacy_lmPerW'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '中为排测机':
            # 读取csv格式文件，按分光机002输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=0, \
                             usecols=['  NO', 'Tc(k)', 'λd(nm)', 'λp(nm)', \
                                      '△λ', 'CIE-x', 'CIE-y', 'Ra', \
                                      'фv(lm)', 'R9', 'P(mw)', 'Vf(V)', \
                                      'I(mA)', 'P(w)', 'Efficiency(lm/w)', \
                                      'SDCM', 'BIN NO.', 'BIN name', \
                                      'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', \
                                      'R10', 'R11', \
                                      'R12', 'R13', 'R14', 'R15', 'Time'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'  NO': 'TestNO', 'Tc(k)': 'CCT_K', 'λd(nm)': 'DominantWavelength_nm', \
                                    'λp(nm)': 'PeakWavelength_nm', '△λ': 'FWHM_nm', 'CIE-x': 'CIEx', 'CIE-y': 'CIEy',
                                    'Ra': 'Ra', \
                                    'фv(lm)': 'LuminousFlux_lm', 'R9': 'R9', 'P(mw)': 'RadiantFlux_mW',
                                    'Vf(V)': 'ForwardVoltage_V', \
                                    'I(mA)': 'Current_mA', 'P(w)': 'Power_W', 'Efficiency(lm/w)': 'LumiousEfficacy_lmPerW', \
                                    'SDCM': 'SDCM', 'BIN NO.': 'BinID', 'BIN name': 'BinName', \
                                    'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7',
                                    'R8': 'R8', \
                                    'R10': 'R10', 'R11': 'R11', \
                                    'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15', 'Time': 'TestTime'})
            # df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '中谱连片分光机':
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=0, \
                             usecols=['No.', 'Bin号', 'Bin代号', \
                                      'IF', 'VF', 'Ф(lm)', \
                                      'η(lm/W)', 'CIE-x', 'CIE-y', \
                                      'Tc', 'Ra', 'CRI9', 'SDCM', \
                                      'P(W)', 'WL.D', 'CIE-u', 'CIE-v', \
                                      "CIE-u'", "CIE-v'", \
                                      'WL.P', 'WL.C', \
                                      'WL.H', 'Фe(mW)', 'CRI1', 'CRI2', \
                                      'CRI3', 'CRI4', 'CRI5', 'CRI6', \
                                      'CRI7', 'CRI8', 'CRI10', 'CRI11', \
                                      'CRI12', 'CRI13', 'CRI14', 'CRI15', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'No.': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    'IF': 'Current_mA', 'VF': 'ForwardVoltage_V', 'Ф(lm)': 'LuminousFlux_lm', \
                                    'η(lm/W)': 'LumiousEfficacy_lmPerW', 'CIE-x': 'CIEx', 'CIE-y': 'CIEy', \
                                    'Tc': 'CCT_K', 'Ra': 'Ra', 'CRI9': 'R9', 'SDCM': 'SDCM', \
                                    'P(W)': 'Power_W', 'WL.D': 'DominantWavelength_nm', 'CIE-u': 'CIEu', 'CIE-v': 'CIEv', \
                                    "CIE-u'": 'CIEu_1976', "CIE-v'": 'CIEv_1976', \
                                    'WL.P': 'PeakWavelength_nm', 'WL.C': 'ComplementaryWavelength_nm', \
                                    'WL.H': 'FWHM_nm', 'Фe(mW)': 'RadiantFlux_mW', 'CRI1': 'R1', 'CRI2': 'R2', \
                                    'CRI3': 'R3', 'CRI4': 'R4', 'CRI5': 'R5', 'CRI6': 'R6', \
                                    'CRI7': 'R7', 'CRI8': 'R8', 'CRI10': 'R10', 'CRI11': 'R11', \
                                    'CRI12': 'R12', 'CRI13': 'R13', 'CRI14': 'R14', 'CRI15': 'R15', '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '中谱单颗分光机':
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=13, \
                             usecols=['No.', 'Bin号', 'Bin代号', \
                                      '温度', 'AOI结果', 'C1:VF', \
                                      'C1:IF', 'C1:P(W)', 'C1:x', 'C1:y', \
                                      'C1:Ф(lm)', 'C1:λd(nm)', \
                                      'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'", \
                                      'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)', \
                                      'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM', \
                                      'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6', \
                                      'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11', \
                                      'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', 'C1:VF1', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'No.': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    '温度': 'TestTemperature', 'AOI结果': 'AoiResult', 'C1:VF': 'ForwardVoltage_V', \
                                    'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx', 'C1:y': 'CIEy', \
                                    'C1:Ф(lm)': 'LuminousFlux_lm', 'C1:λd(nm)': 'DominantWavelength_nm', \
                                    'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv', "C1:u'": 'CIEu_1976',
                                    "C1:v'": 'CIEv_1976', \
                                    'C1:λp(nm)': 'PeakWavelength_nm', 'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                    'C1:λh(nm)': 'FWHM_nm', \
                                    'C1:Фe(mW)': 'RadiantFlux_mW', 'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                    'C1:SDCM': 'SDCM', \
                                    'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3', 'C1:CRI4': 'R4', 'C1:CRI5': 'R5',
                                    'C1:CRI6': 'R6', \
                                    'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9', 'C1:CRI10': 'R10', 'C1:CRI11': 'R11', \
                                    'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14', 'C1:CRI15': 'R15', \
                                    'C1:VF1': 'ZenerVoltage_V', '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '中谱分光一体机#5':
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=15, \
                             usecols=['编号', 'Bin号', 'Bin代号', \
                                      '温度', 'AOI结果', 'C1:VF1', 'C1:VF', \
                                      'C1:IF', 'C1:P(W)', 'C1:x', 'C1:y', \
                                      'C1:Ф(lm)', 'C1:λd(nm)', \
                                      'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'", \
                                      'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)', \
                                      'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM', \
                                      'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6', \
                                      'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11', \
                                      'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'编号': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    '温度': 'TestTemperature', 'AOI结果': 'AoiResult', 'C1:VF1': 'ZenerVoltage_V',
                                    'C1:VF': 'ForwardVoltage_V', \
                                    'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx', 'C1:y': 'CIEy', \
                                    'C1:Ф(lm)': 'LuminousFlux_lm', 'C1:λd(nm)': 'DominantWavelength_nm', \
                                    'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv', "C1:u'": 'CIEu_1976',
                                    "C1:v'": 'CIEv_1976', \
                                    'C1:λp(nm)': 'PeakWavelength_nm', 'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                    'C1:λh(nm)': 'FWHM_nm', \
                                    'C1:Фe(mW)': 'RadiantFlux_mW', 'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                    'C1:SDCM': 'SDCM', \
                                    'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3', 'C1:CRI4': 'R4', 'C1:CRI5': 'R5',
                                    'C1:CRI6': 'R6', \
                                    'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9', 'C1:CRI10': 'R10', 'C1:CRI11': 'R11', \
                                    'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14', 'C1:CRI15': 'R15',
                                    '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '中谱分光一体机#6':
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=18, \
                             usecols=['编号', 'Bin号', 'Bin代号', \
                                      '温度', 'AOI结果', 'C1:VF1', 'C1:VF', \
                                      'C1:IF', 'C1:P(W)', 'C1:x', 'C1:y', \
                                      'C1:Ф(lm)', 'C1:λd(nm)', \
                                      'C1:Tc', 'C1:Ra', 'C1:u', 'C1:v', "C1:u'", "C1:v'", \
                                      'C1:λp(nm)', 'C1:λc(nm)', 'C1:λh(nm)', \
                                      'C1:Фe(mW)', 'C1:η(lm/W)', 'C1:SDCM', \
                                      'C1:CRI1', 'C1:CRI2', 'C1:CRI3', 'C1:CRI4', 'C1:CRI5', 'C1:CRI6', \
                                      'C1:CRI7', 'C1:CRI8', 'C1:CRI9', 'C1:CRI10', 'C1:CRI11', \
                                      'C1:CRI12', 'C1:CRI13', 'C1:CRI14', 'C1:CRI15', '测试时间'])
            # 输出未处理的结果，方便代码调试
            # print(df)
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'编号': 'TestNO', 'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    '温度': 'TestTemperature', 'AOI结果': 'AoiResult', 'C1:VF1': 'ZenerVoltage_V',
                                    'C1:VF': 'ForwardVoltage_V', \
                                    'C1:IF': 'Current_mA', 'C1:P(W)': 'Power_W', 'C1:x': 'CIEx', 'C1:y': 'CIEy', \
                                    'C1:Ф(lm)': 'LuminousFlux_lm', 'C1:λd(nm)': 'DominantWavelength_nm', \
                                    'C1:Tc': 'CCT_K', 'C1:Ra': 'Ra', 'C1:u': 'CIEu', 'C1:v': 'CIEv', "C1:u'": 'CIEu_1976',
                                    "C1:v'": 'CIEv_1976', \
                                    'C1:λp(nm)': 'PeakWavelength_nm', 'C1:λc(nm)': 'ComplementaryWavelength_nm',
                                    'C1:λh(nm)': 'FWHM_nm', \
                                    'C1:Фe(mW)': 'RadiantFlux_mW', 'C1:η(lm/W)': 'LumiousEfficacy_lmPerW',
                                    'C1:SDCM': 'SDCM', \
                                    'C1:CRI1': 'R1', 'C1:CRI2': 'R2', 'C1:CRI3': 'R3', 'C1:CRI4': 'R4', 'C1:CRI5': 'R5',
                                    'C1:CRI6': 'R6', \
                                    'C1:CRI7': 'R7', 'C1:CRI8': 'R8', 'C1:CRI9': 'R9', 'C1:CRI10': 'R10', 'C1:CRI11': 'R11', \
                                    'C1:CRI12': 'R12', 'C1:CRI13': 'R13', 'C1:CRI14': 'R14', 'C1:CRI15': 'R15',
                                    '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif 'ZP' in machine_name:
            # 读取csv格式文件，按分光机001输出的csv选择需用的列数据
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=0,
                             usecols=['No.', 'Bin号', 'Bin代号', 'VF', 'IF', 'P(W)', 'CIE-x', 'CIE-y', 'Tc',
                                      'WL.D', 'Ф(lm)', 'Ra', 'CIE-u', 'CIE-v', "CIE-u'", "CIE-v'",
                                      'WL.P', 'WL.C', 'WL.H', 'Фe(mW)', 'η(lm/W)', 'SDCM',
                                      'CRI1', 'CRI2', 'CRI3', 'CRI4', 'CRI5', 'CRI6',
                                      'CRI7', 'CRI8', 'CRI9', 'CRI10', 'CRI11',
                                      'CRI12', 'CRI13', 'CRI14', 'CRI15', '测试时间'])


             # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(columns={'No.': 'TestNO',
                                                        'Bin号': 'BinID', 'Bin代号': 'BinName', \
                                    'VF': 'ForwardVoltage_V', 'IF': 'Current_mA', 'P(W)': 'Power_W', \
                                    'CIE-x': 'CIEx', 'CIE-y': 'CIEy', 'Tc': 'CCT_K', \
                                    'WL.D': 'DominantWavelength_nm', 'Ф(lm)': 'LuminousFlux_lm', 'Ra': 'Ra', \
                                    'CIE-u': 'CIEu', 'CIE-v': 'CIEv', "CIE-u'": 'CIEu_1976', "CIE-v'": 'CIEv_1976', \
                                    'WL.P': 'PeakWavelength_nm', 'WL.C': 'ComplementaryWavelength_nm', 'WL.H': 'FWHM_nm', \
                                    'Фe(mW)': 'RadiantFlux_mW', 'η(lm/W)': 'LumiousEfficacy_lmPerW', 'SDCM': 'SDCM', \
                                    'CRI1': 'R1', 'CRI2': 'R2', 'CRI3': 'R3', 'CRI4': 'R4', 'CRI5': 'R5', 'CRI6': 'R6', \
                                    'CRI7': 'R7', 'CRI8': 'R8', 'CRI9': 'R9', 'CRI10': 'R10', 'CRI11': 'R11', \
                                    'CRI12': 'R12', 'CRI13': 'R13', 'CRI14': 'R14', 'CRI15': 'R15', '测试时间': 'TestTime'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '远方1号机' and not pluse:
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', sep='\t', header=0, \
                             usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "测试时间", "x", "y",
                                      "CCT(K)", \
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', \
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15',"SDCM", 'Tb'])
            df_test_data = df_test_data.rename(columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W', \
                                    "Φ(lm)": 'LuminousFlux_lm', \
                                    "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW',
                                    "测试时间": 'TestTime', \
                                    "x": 'CIEx', "y": 'CIEy', \
                                    "CCT(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm',
                                    "峰值波长(nm)": 'PeakWavelength_nm', \
                                    "Ra": 'Ra', 'R1': 'R1', \
                                    'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                                    'R9': 'R9', 'R10': 'R10', \
                                    'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',"SDCM": 'SDCM','Tb': 'TestTemperature'})

            df_test_data['Current_mA'] = df_test_data['Current_mA'] * 1000

            # df['TestTime'] = pd.to_datetime(df['TestTime'])

        elif machine_name == '远方1号机' and pluse:
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', header=0,
                             usecols=[" 编号", "IF(mA)", "VF(V)", "P(mW)", "光通量   Φ(lm)", "Φe(mW)", "光效(lm/W)", "时间",
                                      "色坐标 x", "色坐标 y",
                                      "色温(K)", \
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9', \
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15', '环境温度(度)'])
            df_test_data = df_test_data.rename(
                columns={" 编号": 'TestNO', "IF(mA)": 'Current_mA', "VF(V)": 'ForwardVoltage_V', "P(mW)": 'Power_W', \
                         "光通量   Φ(lm)": 'LuminousFlux_lm', \
                         "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW',
                         "时间": 'TestTime', \
                         "色坐标 x": 'CIEx', "色坐标 y": 'CIEy', \
                         "色温(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm',
                         "峰值波长(nm)": 'PeakWavelength_nm', \
                         "Ra": 'Ra', 'R1': 'R1', \
                         'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8', \
                         'R9': 'R9', 'R10': 'R10', \
                         'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',
                         '环境温度(度)': 'TestTemperature'})

            # df['Current_mA'] = df['Current_mA'] * 1000
            df_test_data['Power_W'] = round(df_test_data['Power_W'] / 1000, 4)
            df_test_data['TestTime'] = df_test_data['TestTime'].apply(lambda x: x[:-3] + ":" + x[-2:])
            # df['TestTime'] = pd.to_datetime(df['TestTime'])

        elif machine_name == '远方2号机':
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', sep='\t', header=0, \
                             usecols=["编号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "测试时间", "x", "y", "Tc(K)", \
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9',\
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15','环境温度'])
            df_test_data = df_test_data.rename(columns={"编号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W',
                                    "Φ(lm)": 'LuminousFlux_lm', \
                                    "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "测试时间": 'TestTime',
                                    "x": 'CIEx', "y": 'CIEy', \
                                    "Tc(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm', "峰值波长(nm)": 'PeakWavelength_nm',
                                    "Ra": 'Ra', 'R1': 'R1', \
                                    'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8','R9': 'R9',
                                    'R10': 'R10', \
                                    'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15','环境温度':'TestTemperature'})
            df_test_data['Current_mA'] = df_test_data['Current_mA'] * 1000
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '远方3号机':
            df_test_data = pd.read_csv(filepath_or_buffer=file_path, encoding='gbk', sep='\t', header=0, \
                             usecols=["序号", "I(A)", "U(V)", "P(W)", "Φ(lm)", "Φe(mW)", "光效(lm/W)", "时间", "x", "y", "CCT(K)", \
                                      "主波长(nm)", "峰值波长(nm)", "Ra", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9',\
                                      'R10', 'R11', 'R12', 'R13', 'R14', 'R15',"SDCM", '环境温度'])
            df_test_data = df_test_data.rename(columns={"序号": 'TestNO', "I(A)": 'Current_mA', "U(V)": 'ForwardVoltage_V', "P(W)": 'Power_W',
                                    "Φ(lm)": 'LuminousFlux_lm', \
                                    "Φe(mW)": 'RadiantFlux_mW', "光效(lm/W)": 'LumiousEfficacy_lmPerW', "时间": 'TestTime',
                                    "x": 'CIEx', "y": 'CIEy', \
                                    "CCT(K)": 'CCT_K', "主波长(nm)": 'DominantWavelength_nm', "峰值波长(nm)": 'PeakWavelength_nm',
                                    "Ra": 'Ra', 'R1': 'R1', \
                                    'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8','R9': 'R9',
                                    'R10': 'R10', \
                                    'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15',"SDCM": 'SDCM', '环境温度':'TestTemperature'})
            df_test_data['Current_mA'] = df_test_data['Current_mA'] * 1000
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])

        elif machine_name == '中为积分球':
            df_test_data = pd.read_excel(io=file_path, sheet_name=0, header=0, \
                               usecols=["序号", "电流(mA)", "电压VF(V)", "功率(w)", "光通量(lm)", "光功率(mW)", "光效率(lm/w)", \
                                        "CIE-X", "CIE-Y", "BIN号", "色温(K)", "显色指数", "R9", "SDCM", "主波长(nm)", \
                                        "峰波长(nm)", "时间", 'R1', 'R2', 'R3', 'R4', 'R5', 'R6', 'R7', 'R8', 'R9','R10', \
                                        'R11', 'R12', 'R13', 'R14', 'R15'])
            # 字段重命名，方便数据库写入
            df_test_data = df_test_data.rename(
                columns={"序号": 'TestNO', "电流(mA)": 'Current_mA', "电压VF(V)": 'ForwardVoltage_V', "功率(w)": 'Power_W', \
                         "光通量(lm)": 'LuminousFlux_lm', "光功率(mW)": 'RadiantFlux_mW', "光效率(lm/w)": 'LumiousEfficacy_lmPerW', \
                         "CIE-X": 'CIEx', "CIE-Y": 'CIEy', "BIN号": 'BinName', "色温(K)": 'CCT_K', "显色指数": 'Ra',
                         "SDCM": 'SDCM', \
                         "主波长(nm)": 'DominantWavelength_nm', "峰波长(nm)": 'PeakWavelength_nm', "时间": 'TestTime', \
                         'R1': 'R1', 'R2': 'R2', 'R3': 'R3', 'R4': 'R4', 'R5': 'R5', 'R6': 'R6', 'R7': 'R7', 'R8': 'R8','R9': 'R9',
                         'R10': 'R10', 'R11': 'R11', 'R12': 'R12', 'R13': 'R13', 'R14': 'R14', 'R15': 'R15'})
            df_test_data['TestTime'] = pd.to_datetime(df_test_data['TestTime'])
        else:
            pass
    except Exception as e:
        error_string = e
    return df_test_data, error_string
# if __name__ == '__main__':
# print(get_item_code('5101-20060387'))
# MY2148-026-1208-MS2SP
# print(get_cf('10105030107',2))
# print(get_cf('10105030107', 3))
# print(get_standpart_data('10105020439'))
# print(get_test_data('G:/Work/DP_ZPConfig_gai/ini/中谱一体机分光数据5101-20070017-0-2167-M.csv.csv',4))
# print(cal_coef(get_standpart_data('10105030107'), get_test_data('5101-20060005-1.csv',6)))
# print(cal_end_coef(cal_coef(get_standpart_data('10105030107'), get_test_data('对标20200521-2.xls',0)).loc['校准系数'].to_dict(),
#              get_cf('10105030107',2),get_cf('10105030107', 3)))
# print(cal_end_data(get_test_data('对标20200521-2.xls',0),get_cf('10105030107',3)))

# test_data = get_test_data('5101-20060005-2222.csv', 6)
# test_data = test_data[test_data['Current_mA'] != 0]
# test_data.dropna(subset=['Current_mA'], inplace=True)
# print(test_data)
